Jeudi 03 mai - Vendredi 04 mai

Les Bases de Données et R

Préparer sa session

Trucs et astuces

  • Eviter de changer votre répertoire de travail avec setwd(),
    c.-à-d. démarrer R directement au bon endroit ou définir un "Projet" dans Rstudio.
  • Ne pas utiliser le .Rprofile avec des options modifiant le comportant de R.
  • Désactiver la conversion automatique des chaînes de caractères en facteurs options(stringsAsFactors = FALSE).
  • Ne pas utiliser rm(list = ls()) pour "rafraichir" votre session.
  • Ne pas sauvegarder/charger .Rdata en quittant/démarrant votre session R.

Le tidyverse

Qu'est-ce-que le tidyverse ?

Un ensemble d'outils basés sur une philosophie/grammaire commune.

if (!"tidyverse"%in%rownames(installed.packages())) {
  install.packages("tidyverse")
}
library(tidyverse)

Qu'est-ce-que le tidyverse ?

library(tidyverse) chargera les packages :

Ces packages représentent la base du tidyverse et sont en constante évolution.

tidyverse_update()

tibble

comme un data.frame, mais en mieux !

Pré-requis

library(tidyverse)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

Construire un tibble

tibble(
  x = 1:5, 
  y = 1, 
  z = x ^ 2 + y
)
#> # A tibble: 5 x 3
#>       x     y     z
#>   <int> <dbl> <dbl>
#> 1     1  1.00  2.00
#> 2     2  1.00  5.00
#> 3     3  1.00 10.0 
#> # ... with 2 more rows
data.frame(
  x = 1:5, 
  y = 1, 
  z = x ^ 2 + y
)
#> Error in data.frame(x = 1:5, y = 1, z = x^2 + y): objet 'x' introuvable

Construire un tibble

  • Et avec des noms de variables "exotiques" ?

    data.frame(`1`= 1:3)
    #>   X1
    #> 1  1
    #> 2  2
    #> 3  3
    tibble(
      `;)` = 1:5, 
      `42` = "1", 
      `€` = `;)` ^ 2 + as.numeric(`42`)
    )
    #> # A tibble: 5 x 3
    #>    `;)` `42`    `€`
    #>   <int> <chr> <dbl>
    #> 1     1 1      2.00
    #> 2     2 1      5.00
    #> 3     3 1     10.0 
    #> # ... with 2 more rows

Afficher un data.frame

  • Les méthodes show() et print().

    as.data.frame(mtcars)
    #>                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
    #> Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
    #> Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
    #> Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
    #> Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
    #> Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
    #> Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
    #> Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
    #> Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
    #> Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
    #> Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
    #> Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
    #> Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
    #> Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
    #> Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
    #> Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
    #> Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
    #> Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
    #> Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
    #> Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
    #> Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
    #> Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
    #> Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
    #> AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
    #> Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
    #> Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
    #> Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
    #> Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
    #> Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
    #> Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
    #> Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
    #> Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
    #> Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2
    print(as.data.frame(mtcars))
    #>                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
    #> Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
    #> Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
    #> Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
    #> Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
    #> Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
    #> Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
    #> Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
    #> Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
    #> Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
    #> Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
    #> Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
    #> Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
    #> Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
    #> Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
    #> Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
    #> Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
    #> Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
    #> Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
    #> Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
    #> Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
    #> Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
    #> Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
    #> AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
    #> Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
    #> Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
    #> Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
    #> Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
    #> Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
    #> Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
    #> Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
    #> Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
    #> Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

Afficher un tibble

  • Les méthodes show() et print() .

    as_tibble(mtcars)
    #> # A tibble: 32 x 11
    #>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #> * <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> # ... with 29 more rows
    print(as_tibble(mtcars))
    #> # A tibble: 32 x 11
    #>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #> * <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> # ... with 29 more rows

Afficher un tibble

  • La fonction rownames_to_column() (et column_to_rownames()).

    as_tibble(rownames_to_column(mtcars))
    #> # A tibble: 32 x 12
    #>   rowname       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #>   <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1 Mazda RX4    21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2 Mazda RX4 ~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3 Datsun 710   22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> # ... with 29 more rows
    print(as_tibble(rownames_to_column(mtcars)))
    #> # A tibble: 32 x 12
    #>   rowname       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #>   <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1 Mazda RX4    21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2 Mazda RX4 ~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3 Datsun 710   22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> # ... with 29 more rows

Afficher un tibble : les options

Changer les options d'affichages d'un tibble :

  • via options()

    options(tibble.print_max = n, tibble.print_min = m, dplyr.print_min = p)

    Afficher les n premières lignes, s'il y a plus de m lignes et sur p colonnes.

  • via print()

    print(x = DF, n = n, width = p))
  • dans Rstudio via View()

    View(mtcars)

Sélectionner une variable : $, [ et [[

mtcars$mpg
#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4
mtcars[["mpg"]]
#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4
mtcars[, "mpg"]
#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4

Sélectionner une variable : $, [ et [[

as_tibble(mtcars)$mpg
#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4
as_tibble(mtcars)[["mpg"]]
#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4
as_tibble(mtcars)[, "mpg"]
#> # A tibble: 32 x 1
#>     mpg
#>   <dbl>
#> 1  21.0
#> 2  21.0
#> 3  22.8
#> # ... with 29 more rows

Assurer la rétro-compatibilité du code

De "vielles" fonctions limitées à la classe d'objet data.frame !

tb <- as_tibble(mtcars)
class(tb)
#> [1] "tbl_df"     "tbl"        "data.frame"
class(as.data.frame(tb))
#> [1] "data.frame"

Exercices (Partie I)

  • Comment savoir si un objet est de classe tibble, data.frame ou encore matrix ?
  • Comment se comporte les opérations suivantes sur un data.frame et l'équivalent tibble ?
    Quelle est la différence ?

    df <- data.frame(abc = 1, xyz = "a")
    df$xyz
    df[["xyz"]]
    df[, "xyz"]
    df[, c("abc", "xyz")]
  • Comment extraire une colonne en utilisant une variable ?

    x <- "abc"

Exercices (Partie I)

  • Comment savoir si un objet est de classe tibble, data.frame ou encore matrix ?

    class(dta)

Exercices (Partie I)

  • Comment se comporte les opérations suivantes sur un data.frame et l'équivalent tibble ?
    Quelle est la différence ?

    df1 <- data.frame(abc = 1, xyz = "a")
    df2 <- as_tibble(df1)
    df1$xyz
    df1[["xyz"]]
    df1[, "xyz"]
    df1[, c("abc", "xyz")]

Exercices (Partie I)

  • Comment extraire une colonne en utilisant une variable ?

    x <- "abc"
    df1[, x]
    df1[[x]]
    x <- "abc"
    df2[, x]
    df2[[x]]

Exercices (Partie II)

Manipuler des noms de variables non-conventionnelles :

dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)
  • Récupérez la variable nommée 1.
  • Tracez un nuage de points de 1 par rapport à 2.
  • Créez un nouveau tibble avec une colonne nommée 3, contenant le résultat de 2 divisé par 1.
  • Renommez les colonnes avec leurs noms littéral: "un", "deux" et "trois".

Exercices (Partie II)

Manipuler des noms de variables non-conventionnelles :

dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)
  • Récupérez la variable nommée 1.

    dta$`1`
  • Tracez un nuage de points de 1 par rapport à 2.

    plot(dta$`1`, dta$`2`)
    ggplot(data = dta, aes(x = `1`, y = `2`)) + geom_point(colour ="white")

Exercices (Partie II)

  • Créez un nouveau tibble avec une colonne nommée 3, contenant le résultat de 2 divisé par 1.

    dta <- tibble(
      `1` = 1:10,
      `2` = `1` * 2 + rnorm(length(`1`)),
      `3` = `2`/ `1`
    )
  • Renommez les colonnes avec leurs noms littéral : un, deux et trois, en construisant un nouveau tibble.

    dta <- tibble(
      "un" = 1:10,
      "deux" = un * 2 + rnorm(length(un)),
      "trois" = deux / un
    )

Pour aller plus loin …

%>%

magrittr : "Ceci n'est pas un pipe."

Pré-requis

library(pryr)
library(tidyverse)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

Qu'est-ce-que le "pipe" ?

L'opérateur "pipe" (%>%) provient du package magrittr développé par Stefan Milton Bache.

Par défaut library(tidyverse) s'occupe de charger l'opérateur "pipe".

Exemple :

  • x %>% f équivalent à f(x)
  • x %>% f(y) équivalent à f(x, y)
  • x %>% f %>% g %>% h équivalent à h(g(f(x)))
  • f <- . %>% cos %>% sin équivalent à f <- function(.) sin(cos(.))

Pourquoi utiliser le "pipe" ?

Une aide à :

  • l'écriture du code.
  • la lisibilité du code.

Note : Raccourci Ctrl+M dans Rstudio pour faire apparaître un "pipe".

Conserver les étapes

x <- rnorm(25)
x1 <- sqrt(x)
x2 <- na.exclude(x1)
x3 <- x2<1
x4 <- table(x3)
x5 <- prop.table(x4)
x6 <- which.max(x5)
x7 <- names(x6)


Les inconvénients de cette écriture :

  • Obligation de nommer de façon explicite les différents objets.
  • Environnement surchargé d'objets peu utiles (ls()).
  • Utilisation plus importante de la mémoire vive ?

    mtcars2 <- mtcars %>% mutate(cyl_fac = factor(cyl))
    
    pryr::object_size(mtcars)
    pryr::object_size(mtcars2)
    pryr::object_size(mtcars, mtcars2)

Remplacer l'objet original

x <- rnorm(25)

x <- sqrt(x)
x <- na.exclude(x)
x <- x<1
x <- table(x)
x <- prop.table(x)
x <- which.max(x)
x <- names(x)


Les inconvénients de cette écriture :

  • Obligation de relancer tout le code pour débuguer.
  • Répétition du nom de l'objet (14 fois).

Composer l'appel aux fonctions

x <- rnorm(25)

names(
  which.max(
    prop.table(
      table(
        na.exclude(
          sqrt(x)
        )<1
      )
    )
  )
)

names(which.max(prop.table(table(na.exclude(sqrt(x))<1))))


Les inconvénients de cette écriture :

  • L'ordre des appels va de l'intérieur vers l'extérieur.
  • Les arguments peuvent se situer "loin" de la fonction.

Utiliser le "pipe"

x %>% 
  sqrt() %>% 
  na.exclude() %>% 
  `<`(1) %>% 
  table() %>% 
  prop.table() %>% 
  which.max() %>% 
  names()


A éviter si :

  • le nombre d'étapes devient important.
    Utiliser des objets intermédiaires avec des noms claires.
  • la séquence d'opération n'est pas linéaire.
  • les opérations nécéssitent plusieurs objets en entrée et/ou sortie.

Pour aller plus loin …

vignette(package = "magrittr")

readr & readxl

Importer des données

Pré-requis

library(readxl)
library(tidyverse)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

Les fonctions de base de readr

Lire des tableaux rectangulaires :

  • read_csv() : fichier avec séparateur virgule (".csv").
  • read_tsv() : fichier avec séparateur tabulation.
  • read_table() : fichier avec séparateur espace.
  • read_delim() : forme générale (delim = "").

Lire des données R (".rds") :

  • read_rds() : surcouche de readRDS() (sans compression).

Lire des formats plus exotiques :

  • read_fwf() : fichier à largeur fixe (nombre de caractères).
  • read_log() : fichier de log provenant de serveur web Apache.

Par rapport à celles de R : Entrée

  • Nom homogène des fonctions et arguments.
  • Plus rapide (moins rapide que data.table).
  • Auto-conversion en facteur.
  • L'importation ne dépend pas des variables locales.
locale()
#> <locale>
#> Numbers:  123,456.78
#> Formats:  %AD / %AT
#> Timezone: UTC
#> Encoding: UTF-8
#> <date_names>
#> Days:   Sunday (Sun), Monday (Mon), Tuesday (Tue), Wednesday (Wed), Thursday
#>         (Thu), Friday (Fri), Saturday (Sat)
#> Months: January (Jan), February (Feb), March (Mar), April (Apr), May (May),
#>         June (Jun), July (Jul), August (Aug), September (Sep), October
#>         (Oct), November (Nov), December (Dec)
#> AM/PM:  AM/PM

Par rapport à celles de R : Sortie

  • Conversion des champs "date" au format "%Y-%m-%d".
  • Conversion des champs date/heure dans la norme ISO8601.
    Par exemple :
    • 07:57:59Z
    • 2018-04-13T07:57:59Z
  • Noms des colonnes inchangés.
  • Renvoie un objet tibble.
  • Pas de nom de ligne.

Dans la pratique

Lire un CSV avec base

ratings <- read.csv(
  file = "./materials/ratings.csv"
)
str(ratings[0, ])
#> 'data.frame':    0 obs. of  13 variables:
#>  $ Const         : chr 
#>  $ Your.Rating   : int 
#>  $ Date.Rated    : chr 
#>  $ Title         : chr 
#>  $ URL           : chr 
#>  $ Title.Type    : chr 
#>  $ IMDb.Rating   : num 
#>  $ Runtime..mins.: int 
#>  $ Year          : int 
#>  $ Genres        : chr 
#>  $ Num.Votes     : int 
#>  $ Release.Date  : chr 
#>  $ Directors     : chr

Dans la pratique

Lire un CSV avec readr

ratings <- read_csv(
  file = "./materials/ratings.csv"
)
#> Parsed with column specification:
#> cols(
#>   Const = col_character(),
#>   `Your Rating` = col_integer(),
#>   `Date Rated` = col_date(format = ""),
#>   Title = col_character(),
#>   URL = col_character(),
#>   `Title Type` = col_character(),
#>   `IMDb Rating` = col_double(),
#>   `Runtime (mins)` = col_integer(),
#>   Year = col_integer(),
#>   Genres = col_character(),
#>   `Num Votes` = col_integer(),
#>   `Release Date` = col_date(format = ""),
#>   Directors = col_character()
#> )

Dans la pratique

Lire un "texte" avec base

read.csv(
  text = "a,b,c
    1,2,3
    4,5,6"
)
#>   a b c
#> 1 1 2 3
#> 2 4 5 6

Dans la pratique

Lire un "texte" avec readr

read_csv(
  "a,b,c
  1,2,3
  4,5,6"
)
#> # A tibble: 2 x 3
#>       a     b     c
#>   <int> <int> <int>
#> 1     1     2     3
#> 2     4     5     6

Les arguments de readr

  • L'argument skip = n.

    read_csv("Une ligne de métadonnées
      x,y,z
      1,2,3", skip = 1)
    #> # A tibble: 1 x 3
    #>       x     y     z
    #>   <int> <int> <int>
    #> 1     1     2     3
  • L'argument comment = "#".

    read_csv("# Une ligne de commentaires
      x,y,z
      1,2,3", comment = "#")
    #> # A tibble: 1 x 3
    #>       x     y     z
    #>   <int> <int> <int>
    #> 1     1     2     3

Les arguments de readr

  • L'argument col_names = FALSE.

    read_csv("1,2,3\n4,5,6", col_names = FALSE)
    #> # A tibble: 2 x 3
    #>      X1    X2    X3
    #>   <int> <int> <int>
    #> 1     1     2     3
    #> 2     4     5     6
  • Utiliser col_names pour nommer les colonnes.

    read_csv("1,2,3\n4,5,6", col_names = c("a", "b", "c"))
    #> # A tibble: 2 x 3
    #>       a     b     c
    #>   <int> <int> <int>
    #> 1     1     2     3
    #> 2     4     5     6

Les arguments de readr

  • L'argument na = ".".

    read_csv("a,b,c\n1,2,3\n4,.,6", na = ".")
    #> # A tibble: 2 x 3
    #>       a     b     c
    #>   <int> <int> <int>
    #> 1     1     2     3
    #> 2     4    NA     6
  • L'argument col_types.

    read_csv(
      "a,b,c\n1,x,2018-05-03\n4,y,2018-05-04", 
      col_types = list(col_integer(), col_character(), col_date(format = "%Y-%m-%d"))
    )
    #> # A tibble: 2 x 3
    #>       a b     c         
    #>   <int> <chr> <date>    
    #> 1     1 x     2018-05-03
    #> 2     4 y     2018-05-04

Exercices

  • Quelle fonction utiliseriez-vous pour importer un document avec | en séparateur ?

  • Un fichier CSV peut parfois contenir une virgule dans un champ.
    Astuce : utilisez des guillemets simple ' ou double ".
    1. Quel est le caractère par défaut utiliser par read_csv() ?
    2. Quelle fonction et argument utiliser pour changer ce caractère ?
    3. Quelle fonction et avec quelle valeur argument liriez-vous la chaîne suivante ?
    "x,y\n1,'a,b'"
  • Identifiez les problèmes des lignes suivantes.

    read_csv("a,b\n1,2,3\n4,5,6")
    read_csv("a,b,c\n1,2\n1,2,3,4")
    read_csv("a,b\n\"1")
    read_csv("a,b\n1,2\na,b")
    read_csv("a;b\n1;3")

Exercices

  • Quelle fonction utiliseriez-vous pour importer un document avec | en séparateur ?

    read_delim(file = path, delim = "|")

Exercices

  • Un fichier CSV peut parfois contenir une virgule dans un champ.
    Astuce : utilisez des guillemets simple ' ou double ".
    1. Quel est le caractère par défaut utiliser par read_csv() ?
    r "
    1. Quelle fonction et argument utiliser pour changer ce caractère ?
    r quote = "\""
    1. Quelle fonction et avec quelle valeur argument liriez-vous la chaîne suivante ?
    read_csv("x,y\n1,'a,b'", quote = "\'")
    read_delim("x,y\n1,'a,b'", delim = ",", quote = "\'")

Exercices

  • Identifiez les problèmes des lignes suivantes.

    read_csv("a,b\n1,2,3\n4,5,6")
    read_csv("a,b,c\n1,2\n1,2,3,4")
    read_csv("a,b\n\"1")
    read_csv("a,b\n1,2\na,b")
    read_csv("a;b\n1;3")

Les alternatives à readr

Type de données Package Alternatives
Fichier "Texte" readr base, data.table
Excel readxl gdata, openxlsx, XLConnect, xlsx
Logiciel Statistique haven foreign, sas7bdat, readstata13

Quelques benchmark !

Quelques benchmark : Lecture

Quelques benchmark : Ecriture

Exporter des données vers un fichier

readr inclut également des fonctions d'écriture write_*().

  • Les chaînes de caractères sont en UTF-8.
  • Les dates et date/heure sont au format ISO8601.

Exemple :

readr::write_csv(x = ratings, path = "ratings.csv")
writexl::write_xlsx(x = ratings, path = "ratings.xlsx")

dplyr

Une grammaire de la manipulation des données

dplyr : Le pipe et des fonctions utiles

Pré-requis

library(tidyverse)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

Les fonctions de base

5 (+1) fonctions clés de dplyr :

  1. Sélectionner des observations par valeur (filter()).
  2. Trier les lignes (arrange()).
  3. Sélectionner des colonnes (select()).
  4. Ajouter des colonnes en fonction des colonnes préexistantes (mutate()).
  5. Résumer des valeurs (summarise()).

  6. group_by() pour travailler sur des sous-ensembles d'un jeu de données.

Les fonctions de base

Une syntaxe commune :

  • le premier argument est un objet data.frame.
  • Le second argument décrit l'action à effectuer (noms de variable sans guillemets).
  • Renvoie un data.frame (ou tibble).

Sélectionner des lignes

ratings <- read_csv(file = "./materials/ratings.csv")
filter(ratings, `Your Rating` >= 9, `IMDb Rating` >= 9)
#> # A tibble: 1 x 13
#>   Const  `Your Rating` `Date Rated` Title   URL      `Title Type` `IMDb Rating`
#>   <chr>          <int> <date>       <chr>   <chr>    <chr>                <dbl>
#> 1 tt011~             9 2014-11-08   The Sh~ https:/~ movie                 9.30
#> # ... with 6 more variables: `Runtime (mins)` <int>, Year <int>, Genres <chr>,
#> #   `Num Votes` <int>, `Release Date` <date>, Directors <chr>
ratings %>% 
  filter(`Your Rating` >= 9 & `IMDb Rating` >= 9)
#> # A tibble: 1 x 13
#>   Const  `Your Rating` `Date Rated` Title   URL      `Title Type` `IMDb Rating`
#>   <chr>          <int> <date>       <chr>   <chr>    <chr>                <dbl>
#> 1 tt011~             9 2014-11-08   The Sh~ https:/~ movie                 9.30
#> # ... with 6 more variables: `Runtime (mins)` <int>, Year <int>, Genres <chr>,
#> #   `Num Votes` <int>, `Release Date` <date>, Directors <chr>

Exercices

  • Trouvez le film le plus noté.

  • Trouvez les films contenant "shark" dans le titre (grepl()).

  • Trouvez les films vus entre le 3 Mai 2018 et le 25 Mai 2018.

  • Que fait la fonction between() ?

Exercices

  • Trouvez le film le plus noté.

    ratings %>% 
      filter(`Num Votes`==max(`Num Votes`))
  • Trouvez les films contenant "shark" dans le titre (grepl()).

    ratings %>% 
      filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
  • Trouvez les films vus entre le 3 Mai 2018 et le 25 Mai 2018.

    ratings %>% 
      filter(`Date Rated`>="2018-04-25" & `Date Rated`<="2018-05-03")
  • Que fait la fonction between() ?

    ratings %>% 
      filter(between(`Date Rated`, "2018-04-25", "2018-05-03"))

Réordonner des lignes

ratings %>% 
  arrange(desc(`Date Rated`))
#> # A tibble: 2,229 x 13
#>    Const  `Your Rating` `Date Rated` Title  URL      `Title Type` `IMDb Rating`
#>    <chr>          <int> <date>       <chr>  <chr>    <chr>                <dbl>
#>  1 tt723~             2 2018-04-13   Taxi 5 https:/~ movie                 5.60
#>  2 tt331~             2 2018-04-08   Game ~ https:/~ movie                 5.40
#>  3 tt509~             5 2018-04-08   Dans ~ https:/~ movie                 6.20
#>  4 tt287~             5 2018-04-05   Red S~ https:/~ movie                 6.80
#>  5 tt699~             2 2018-04-04   Gasto~ https:/~ movie                 3.90
#>  6 tt536~             5 2018-03-31   Mary ~ https:/~ movie                 6.00
#>  7 tt167~             6 2018-03-28   Ready~ https:/~ movie                 7.90
#>  8 tt678~             3 2018-03-28   Dolph~ https:/~ movie                 6.40
#>  9 tt641~             2 2018-03-24   Eva    https:/~ movie                 4.90
#> 10 tt714~             5 2018-03-24   La fi~ https:/~ movie                 6.20
#> # ... with 2,219 more rows, and 6 more variables: `Runtime (mins)` <int>,
#> #   Year <int>, Genres <chr>, `Num Votes` <int>, `Release Date` <date>,
#> #   Directors <chr>

Exercices

  • Réordonnez ratings selon les notes IMDb (ÌMDb Rating) et utilisateur (Your Rating).

  • Réordonnez ratings selon les titres des films (Title), par ordre alphabétique.

Exercices

  • Réordonnez ratings selon les notes IMDb (ÌMDb Rating) et utilisateur (Your Rating).

    ratings %>% 
      arrange(desc(`ÌMDb Rating`), desc(`Your Rating`))
  • Réordonnez ratings selon les titres des films (Title), par ordre alphabétique.

    ratings %>% 
      arrange(Title)

Sélectionner des colonnes

Des aides à la sélection :

  • starts_with("abc") : noms commençant par "abc".
  • ends_with("xyz") : noms finissant par "xyz".
  • contains("ijk") : noms contenant "ijk".
  • matches("(.)\\1") : noms correspondant à l'expression régulière.
  • num_range("x", 1:3): noms correspondant à x1, x2 et x3.
ratings %>% 
  arrange(desc(`Date Rated`)) %>% 
  select(Title, ends_with("Rating"))
#> # A tibble: 2,229 x 3
#>   Title           `Your Rating` `IMDb Rating`
#>   <chr>                   <int>         <dbl>
#> 1 Taxi 5                      2          5.60
#> 2 Game Over, Man!             2          5.40
#> 3 Dans la brume               5          6.20
#> 4 Red Sparrow                 5          6.80
#> # ... with 2,225 more rows

Exercices

  • Sélectionnez les colonnes contenant "Date".

  • Sélectionnez les colonnes contenant "Title", "Your Rating", "Date Rated" et "Release Date" par leu indice.

Exercices

  • Sélectionnez les colonnes contenant "Date".

    ratings %>% 
      select(contains("Date"))
  • Sélectionnez les colonnes contenant "Title", "Your Rating", "Date Rated" et "Release Date" par leu indice.

    ratings %>% 
      select(4, 2, 3, 12)

Ajouter des colonnes

ratings %>% 
  arrange(desc(`Date Rated`)) %>% 
  mutate(Rating_is_better = `Your Rating`>=`IMDb Rating`) %>% 
  select(Title, `Your Rating`, `IMDb Rating`, Rating_is_better)
#> # A tibble: 2,229 x 4
#>    Title            `Your Rating` `IMDb Rating` Rating_is_better
#>    <chr>                    <int>         <dbl> <lgl>           
#>  1 Taxi 5                       2          5.60 F               
#>  2 Game Over, Man!              2          5.40 F               
#>  3 Dans la brume                5          6.20 F               
#>  4 Red Sparrow                  5          6.80 F               
#>  5 Gaston Lagaffe               2          3.90 F               
#>  6 Mary Magdalene               5          6.00 F               
#>  7 Ready Player One             6          7.90 F               
#>  8 Dolphins                     3          6.40 F               
#>  9 Eva                          2          4.90 F               
#> 10 La finale                    5          6.20 F               
#> # ... with 2,219 more rows

Exercices

  • Calculez la moyenne des notes Your Ratinget IMDb Rating.

  • Ajoutez les mois month et jours days à partir de Date Rated (package lubridate).

Exercices

  • Calculez la moyenne des notes Your Ratinget IMDb Rating.

    ratings %>% 
      mutate(`Avg Rating` = (`Your Rating`+`IMDb Rating`)/2)
  • Ajoutez les mois month et jours days à partir de Date Rated (package lubridate).

    ratings %>% 
      mutate(
        Month = lubridate::month(`Date Rated`),
        day = lubridate::day(`Date Rated`)
      )

Résumer les données

read_csv(file = "./materials/ratings.csv") %>%  
  summarise(
    n_movies = n(),
    my_average_rating = mean(`Your Rating`),
    oldest_rating = min(`Date Rated`),
    newest_rating =  max(`Date Rated`)
  )
#> # A tibble: 1 x 4
#>   n_movies my_average_rating oldest_rating newest_rating
#>      <int>             <dbl> <date>        <date>       
#> 1     2229              5.60 2014-11-08    2018-04-13

Exercices

  • Calculez la moyenne, l'écart-type, le minimum et le maximum des notes de IMDb Rating.

  • Calculez le nombre de jours séparant la première note de la dernière.

Exercices

  • Calculez la moyenne, l'écart-type, le minimum et le maximum des notes de IMDb Rating.

    ratings %>% 
      summarise(
        mean = mean(`IMDb Rating`),
        sd = sd(`IMDb Rating`),
        min = min(`IMDb Rating`),
        max = max(`IMDb Rating`)
      )
  • Calculez le nombre de jours séparant la première note de la dernière.

    ratings %>% 
      summarise(
        ndays = max(`Date Rated`)-min(`Date Rated`)
      )

Utiliser group_by()

read_csv(file = "./materials/ratings.csv") %>% 
  mutate(
    Year = lubridate::year(`Date Rated`)
  ) %>% 
  filter(Year!="2014") %>% 
  group_by(Year) %>% 
  summarise(
    n_movies = n(),
    my_average_rating = mean(`Your Rating`),
    users_average_rating = mean(`IMDb Rating`)
  ) %>% 
  arrange(desc(Year))
#> # A tibble: 4 x 4
#>    Year n_movies my_average_rating users_average_rating
#>   <dbl>    <int>             <dbl>                <dbl>
#> 1  2018       52              4.98                 6.42
#> 2  2017      204              5.12                 6.44
#> 3  2016      238              5.13                 6.48
#> 4  2015      374              5.07                 6.19

Exercices

  • Calculez le temps total (Runtime (mins)) et le nombre de films pour l'année 2017, par note (Your Rating). Repartez du fichier CSV et triez le résultat par ordre décroissant de la variable de temps total calculée.

Exercices

  • Calculez le temps total (Runtime (mins)) et le nombre de films pour l'année 2017, par note (Your Rating). Repartez du fichier CSV et triez le résultat par ordre décroissant de la variable de temps total calculée.

    read_csv(file = "./materials/ratings.csv") %>% 
      mutate(
        Year = lubridate::year(`Date Rated`)
      ) %>% 
      filter(Year=="2017") %>% 
      group_by(`Your Rating`) %>% 
      summarise(
        n_movies = n(),
        runtime_all = sum(`Runtime (mins)`)
      ) %>% 
      arrange(des(runtime_all))

Pour aller plus loin …

DBI

Importer depuis une base de données

Pré-requis

library(DBI)
library(odbc)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

Démarrer PostgreSQL

  • Lancez PostgreSQL via le raccourci "SQL Shell (psql)".

  • Laissez les valeurs par défaut (pressez "Entrée").

Démarrer PostgreSQL

Pour retirer le message d'avertissement, ajouter "chcp 1252" dans le fichier ".bat".

Commandes psql

  • \? pour l'aide des commandes psql.
  • \q quitter.
  • \h aide des commandes sql.
  • \l liste des bases de données.
  • \c se connecter à une base.
  • \d [nom] pour la description d'une table, d'un index, séquence, vue.
  • \d liste des relations (tables, vues et séquences).
  • \i nom_fichier.sql exécuter un fichier de commandes SQL.

Base, utilisateur et droits en SQL

  • Créer un utilisateur.

    CREATE ROLE rdb LOGIN password 'rdbpass';
  • Créer une base.

    CREATE DATABASE datawarehouse;
  • Modifier le propriétaire d'une base.

    ALTER DATABASE datawarehouse OWNER TO 'rdb';

Base, utilisateur et droits en R

Des connecteurs :

Base, utilisateur et droits en R

  • Identifier le driver de connection.

    odbcListDrivers() %>% 
      filter(attribute=="DriverODBCVer") %>% 
      select(-attribute)
    #>                                                     name      value
    #> 1                                             SQL Server      03.50
    #> 2               Microsoft Access Driver (*.mdb, *.accdb)      02.50
    #> 3 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)      02.50
    #> 4            Microsoft Access Text Driver (*.txt, *.csv)      02.50
    #> 5                           PostgreSQL ODBC Driver(ANSI) 10.01.0000
    #> 6                        PostgreSQL ODBC Driver(UNICODE) 10.01.0000

Base, utilisateur et droits en R

  • Initier la connection.

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      port = 5432,
      database = "postgres",
      uid = "postgres",
      password = "password"
    )
    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      port = 5432,
      database = "postgres",
      uid = "postgres",
      password = rstudioapi::askForPassword("Mot de passe :")
    )

Base, utilisateur et droits en R

  • Créer un utilisateur.

    dbSendQuery(con, SQL("CREATE ROLE test_user LOGIN password 'test_pwd';"))
    #> <OdbcResult>
    #>   SQL  CREATE ROLE test_user LOGIN password 'test_pwd';
    #>   ROWS Fetched: 0 [complete]
    #>        Changed: 71285632
  • Créer une base.

    dbSendQuery(con, SQL("CREATE DATABASE test_data;"))
    #> Warning in new_result(connection@ptr, statement): Cancelling previous query
    #> <OdbcResult>
    #>   SQL  CREATE DATABASE test_data;
    #>   ROWS Fetched: 0 [complete]
    #>        Changed: 71285632

Base, utilisateur et droits en R

  • Modifier le propriétaire d'une base.

    dbSendQuery(con, SQL("ALTER DATABASE test_data OWNER TO test_user;"))
    #> Warning in new_result(connection@ptr, statement): Cancelling previous query
    #> <OdbcResult>
    #>   SQL  ALTER DATABASE test_data OWNER TO test_user;
    #>   ROWS Fetched: 0 [complete]
    #>        Changed: 71285632
  • Se déconnecter de la base.

    dbDisconnect(con)
    #> Warning in connection_release(conn@ptr): There is a result object still in use.
    #> The connection will be automatically released when it is closed

Créer des tables en SQL depuis R

  • Se connecter en tant que "test_user".

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "test_data",
      port = 5432,
      uid = "test_user",
      password = "test_pwd"
    )

Créer des tables en SQL depuis R

  • Lister les tables.

    dbListTables(con)
    #> character(0)
  • Créer la table mtcars.

    mtcars %>% 
      rownames_to_column(var = "car") %>% 
      mutate(const = gsub(" .*", "", car)) %>% 
      select(const, everything()) %>% 
      dbWriteTable(con, "mtcars", .)
    
  • Lister les tables.

    dbListTables(con)
    #> [1] "mtcars"

Faire des requêtes en SQL depuis R

  • Lister les champs d'une table.

    dbListFields(con, "mtcars")
    #>  [1] "const" "car"   "mpg"   "cyl"   "disp"  "hp"    "drat"  "wt"    "qsec" 
    #> [10] "vs"    "am"    "gear"  "carb"
  • Lire une table.

    dbReadTable(con, "mtcars") %>% 
      as_tibble()
    #> # A tibble: 32 x 13
    #>   const car     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #>   <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1 Mazda Mazd~  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2 Mazda Mazd~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3 Dats~ Dats~  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> 4 Horn~ Horn~  21.4  6.00   258 110    3.08  3.22  19.4  1.00  0     3.00  1.00
    #> 5 Horn~ Horn~  18.7  8.00   360 175    3.15  3.44  17.0  0     0     3.00  2.00
    #> # ... with 27 more rows

Faire des requêtes en SQL depuis R

  • Sélectioner les voitures du constructeur Mercedes.

    res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE const='Merc'")
    dbFetch(res)
    #>   const         car  mpg cyl  disp  hp drat   wt qsec vs am gear carb
    #> 1  Merc   Merc 240D 24.4   4 146.7  62 3.69 3.19 20.0  1  0    4    2
    #> 2  Merc    Merc 230 22.8   4 140.8  95 3.92 3.15 22.9  1  0    4    2
    #> 3  Merc    Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
    #> 4  Merc   Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
    #> 5  Merc  Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
    #> 6  Merc  Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
    #> 7  Merc Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3
  • Libérer les ressources occupées par la requête.

    dbClearResult(res)
    dbDisconnect(con)

Exercices

  • En tant qu'utilisateur "postgres", créez une base "datawarehouse".
    Créez un utilisateur "data_user".
    Changez le propriétaire de "datawarehouse" par "data_user".

  • En tant qu'utilisateur "data_user", exportez le fichier "ratings.csv" vers la base. Remplacez les espaces par des underscores dans les noms de colonnes.

  • Listez les noms de colonnes de la table créée.

  • Utilisez une requête SQL pour compter le nombre de films pour chaque note (Your Rating)
    Importer le résultat localement sous la forme d'un tibble.

  • Sélectionnez les films notés entre le 1er janvier 2017 et le 31 décembre 2017.
    Exportez cette table vers la base de données.

Exercices

  • En tant qu'utilisateur "postgres", créez une base "datawarehouse". Créez un utilisateur "data_user".
    Changez le propriétaire de "datawarehouse" par "data_user".

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "postgres",
      port = 5432,
      uid = "postgres",
      password = "password"
    )
    dbSendQuery(con, SQL("CREATE ROLE data_user LOGIN password 'data_pwd';"))
    dbSendQuery(con, SQL("CREATE DATABASE datawarehouse;"))
    dbSendQuery(con, SQL("ALTER DATABASE datawarehouse OWNER TO data_user;"))
    dbDisconnect(con)

Exercices

  • En tant qu'utilisateur "data_user", exportez le fichier "ratings.csv" vers la base. Enlevez les espaces dans les noms de colonnes.

    ratings <- read_csv(file = "./materials/ratings.csv") %>% 
      `colnames<-`(gsub(" ", "", colnames(.)))
    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "datawarehouse",
      port = 5432,
      uid = "data_user",
      password = "data_pwd"
    )
    dbWriteTable(con, "ratings", ratings)

Exercices

  • Listez les noms de colonnes de la table créée.

    dbListFields(con, 'ratings')

Exercices

  • Utilisez une requête SQL pour compter le nombre de films pour chaque note ("YourRating").
    Importer le résultat localement sous la forme d'un tibble.

    dbSendQuery(
      conn = con, 
      statement = SQL(
        'SELECT "YourRating", COUNT(*) as count FROM ratings GROUP BY "YourRating";'
      )
    ) %>% 
      dbFetch() %>% 
      as_tibble()

Exercices

  • Sélectionnez les films notés entre le 1er janvier 2017 et le 31 décembre 2017.
    Exportez cette table vers la base de données.

    dbSendQuery(
      conn = con, 
      statement = SQL(
        'SELECT * 
          FROM ratings 
          WHERE "DateRated">=\'2017-01-01\' AND "DateRated"<=\'2017-12-31\';'
      )
    ) %>% 
      dbFetch()%>% 
      dbWriteTable(con, "ratings2017", .)
    
    dbListTables(con)

Pour aller plus loin …

Liste de diffusion autour du package DBI: r-sig-db.

vignette(package = "DBI")

dplyr & dbplyr

Une grammaire SQL généralisée

Pré-requis

library(DBI)
library(odbc)
library(tidyverse)
library(dbplyr)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dbplyr::ident()     masks dplyr::ident()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dbplyr::sql()       masks dplyr::sql()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

dbplyr : Premiers pas avec dplyr

  • Connection sur une base de données SQL intégrée (package RSQLite).

    con <- dbConnect(RSQLite::SQLite(), path = ":memory:")
  • Connection à la base PostgreSQL.

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "datawarehouse",
      port = 5432,
      uid = "data_user",
      password = "data_pwd"
    )

dbplyr : Premiers pas avec dplyr

  • Remplir la base.

    ratings <- read_csv(file = "./materials/ratings.csv") %>% 
      `colnames<-`(gsub(" ", "", colnames(.))) %>% 
      mutate(Year = lubridate::year(DateRated))
    copy_to(
      dest = con, 
      df = ratings, 
      name = "ratings_idx",
      temporary = FALSE, 
      indexes = list(
        "DateRated", 
        "YourRating", 
        "Title", 
        "Year"
      ),
      overwrite = TRUE
    )

Note : temporary = TRUE permet de rendre la table uniquement disponible à la connection en cours et uniquement pendant celle-ci.

dbplyr : Premiers pas avec dplyr

  • Faire une référence à une table depuis une connection.

    ratings_db <- tbl(con, "ratings")
    class(ratings_db)
    #> [1] "tbl_dbi"  "tbl_sql"  "tbl_lazy" "tbl"
  • Afficher l'objet.

    ratings_db
    #> # Source:   table<ratings> [?? x 13]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   Const  YourRating DateRated  Title URL   TitleType IMDbRating `Runtime(mins)`
    #>   <chr>       <int> <date>     <chr> <chr> <chr>          <dbl>           <int>
    #> 1 tt100~          5 2014-12-17 He's~ http~ movie           6.40             129
    #> 2 tt100~          7 2014-11-08 Mega~ http~ movie           7.30              95
    #> 3 tt010~          3 2014-11-26 The ~ http~ movie           5.10              90
    #> 4 tt100~          7 2014-12-26 Ca$h  http~ movie           6.00             100
    #> # ... with more rows, and 5 more variables: Year <int>, Genres <chr>,
    #> #   NumVotes <int>, ReleaseDate <date>, Directors <chr>

Manipuler une base avec dplyr

  • Faire des requêtes sans SQL.

    date_sevendaysago <- (Sys.Date()-7)
    
    ratings_db %>% 
      filter(DateRated > date_sevendaysago) %>% 
      select(Title, DateRated, YourRating, IMDbRating)
    #> # Source:   lazy query [?? x 4]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #> # ... with 4 variables: Title <chr>, DateRated <date>, YourRating <int>,
    #> #   IMDbRating <dbl>

Manipuler une base avec dplyr

  • Faire des opérations en SQL sans SQL.

    ratings_db %>% 
      group_by(Year) %>% 
      summarise(
        N = n(),
        AvgRating = mean(YourRating)
      ) %>% 
      arrange(Year)
    #> Warning: Missing values are always removed in SQL.
    #> Use `AVG(x, na.rm = TRUE)` to silence this warning
    #> # Source:     lazy query [?? x 3]
    #> # Database:   postgres [data_user@localhost:/datawarehouse]
    #> # Ordered by: Year
    #>    Year N               AvgRating
    #>   <int> <S3: integer64>     <dbl>
    #> 1  1936 1                    8.00
    #> 2  1937 1                    7.00
    #> 3  1940 1                    7.00
    #> 4  1941 1                    7.00
    #> # ... with more rows

Manipuler une base avec dplyr

  • dplyr et tbl_df.

    ratings_summary <- ratings %>% 
      group_by(Year) %>% 
      summarise(
        N = n(),
        AvgRating = mean(YourRating)
      ) %>% 
      arrange(Year)
    str(ratings_summary, 1)
    #> Classes 'tbl_df', 'tbl' and 'data.frame':    5 obs. of  3 variables:
    #>  $ Year     : num  2014 2015 2016 2017 2018
    #>  $ N        : int  1361 374 238 204 52
    #>  $ AvgRating: num  5.93 5.07 5.13 5.12 4.98

Manipuler une base avec dplyr

  • dplyr et tbl_sql.

    ratings_summary_db <- ratings_db %>% 
      group_by(Year) %>% 
      summarise(
        N = n(),
        AvgRating = mean(YourRating)
      ) %>% 
      arrange(Year)
    str(ratings_summary_db, 1)
    #> List of 2
    #>  $ src:List of 2
    #>   ..- attr(*, "class")= chr [1:3] "src_dbi" "src_sql" "src"
    #>  $ ops:List of 4
    #>   ..- attr(*, "class")= chr [1:3] "op_arrange" "op_single" "op"
    #>  - attr(*, "class")= chr [1:4] "tbl_dbi" "tbl_sql" "tbl_lazy" "tbl"

Manipuler une base avec dplyr

  • L'envoi de la requête ne s'effectue qu'à la fin ou à l'affichage de l'objet.

    ratings_summary_db %>% 
      print()
    #> Warning: Missing values are always removed in SQL.
    #> Use `AVG(x, na.rm = TRUE)` to silence this warning
    #> # Source:     lazy query [?? x 3]
    #> # Database:   postgres [data_user@localhost:/datawarehouse]
    #> # Ordered by: Year
    #>    Year N               AvgRating
    #>   <int> <S3: integer64>     <dbl>
    #> 1  1936 1                    8.00
    #> 2  1937 1                    7.00
    #> 3  1940 1                    7.00
    #> 4  1941 1                    7.00
    #> # ... with more rows

Manipuler une base avec dplyr

  • Les données ne sont jamais téléchargées dans R (sauf mention explicite).

    ratings_summary_db %>% 
      collect()
    #> Warning: Missing values are always removed in SQL.
    #> Use `AVG(x, na.rm = TRUE)` to silence this warning
    #> # A tibble: 66 x 3
    #>    Year N               AvgRating
    #>   <int> <S3: integer64>     <dbl>
    #> 1  1936 1                    8.00
    #> 2  1937 1                    7.00
    #> 3  1940 1                    7.00
    #> 4  1941 1                    7.00
    #> # ... with 62 more rows

Retour au SQL avec dplyr

  • Que fait dplyr pour effectuer la commande précédente ratings_summary_db ?

     ratings_summary_db %>% 
      show_query()
    #> Warning: Missing values are always removed in SQL.
    #> Use `AVG(x, na.rm = TRUE)` to silence this warning
    #> <SQL>
    #> SELECT "Year", COUNT(*) AS "N", AVG("YourRating") AS "AvgRating"
    #> FROM "ratings"
    #> GROUP BY "Year"
    #> ORDER BY "Year"
  • Traduire du R en requête SQL.

    translate_sql(head(ratings_db))
    #> <SQL> HEAD("ratings_db")

Exercices

  • Comment dbplyr traduit les fonctions as.numeric() et as.character() ?

  • Comment dbplyr traduit l'opérateur ^ ?

  • Comment dbplyr traduit les fonctions mean()et sd() ?
    Et avecna.rm = TRUE ?

Exercices

  • Comment dbplyr traduit les fonctions as.numeric() et as.character() ?

    translate_sql(as.numeric(x))
    #> <SQL> CAST("x" AS NUMERIC)
    translate_sql(as.character(x))
    #> <SQL> CAST("x" AS TEXT)

Exercices

  • Comment dbplyr traduit l'opérateur ^ ?

    translate_sql(x^2)
    #> <SQL> POWER("x", 2.0)
    tbl(con, sql('select 1 as x')) %>%
      mutate(sqr = x^2)
    #> # Source:   lazy query [?? x 2]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>       x   sqr
    #>   <int> <dbl>
    #> 1     1  1.00

Exercices

  • Comment dbplyr traduit les fonctions mean()et sd() ?
    Et avec trim = 0.05 ou na.rm = TRUE ?

    translate_sql(mean(x))
    #> Warning: Missing values are always removed in SQL.
    #> Use `avg(x, na.rm = TRUE)` to silence this warning
    #> <SQL> avg("x") OVER ()
    translate_sql(mean(x, trim = 0.05))
    #> Error in mean(x, trim = 0.05): argument inutilisé (trim = 0.05)
    translate_sql(mean(x, na.rm = TRUE))
    #> <SQL> avg("x") OVER ()

dplyr > SQL > tbl ? > collect() > tbl

  • Rappatrier les données d'une requête via collect()

    ratings_summary <- ratings_summary_db %>% 
      collect()
    #> Warning: Missing values are always removed in SQL.
    #> Use `AVG(x, na.rm = TRUE)` to silence this warning
    class(ratings_summary)
    #> [1] "tbl_df"     "tbl"        "data.frame"

dplyr > SQL > tbl ? > collect() > tbl

  • Connaître les dimensions de l'objet avant collect() ?

    tally(ratings_summary_db)
    #> Warning: Missing values are always removed in SQL.
    #> Use `AVG(x, na.rm = TRUE)` to silence this warning
    #> # Source:   lazy query [?? x 1]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   n              
    #>   <S3: integer64>
    #> 1 66

Exercices

  • Utilisez les fonctions head() , tail() et nrow() sur l'une des tables exportées.
    Que se passe-t'il et pourquoi ?

Exercices

  • Utilisez les fonctions head() , tail() et nrow() sur l'une des tables exportées.
    Que se passe-t'il et pourquoi ?

    ratings_summary_db %>% head(1)
    #> # Source:     lazy query [?? x 3]
    #> # Database:   postgres [data_user@localhost:/datawarehouse]
    #> # Ordered by: Year
    #>    Year N               AvgRating
    #>   <int> <S3: integer64>     <dbl>
    #> 1  1936 1                    8.00
    ratings_summary_db %>% tail()
    #> Error: tail() is not supported by sql sources
    ratings_summary_db %>% nrow()
    #> [1] NA

dplyr, explain() to me

explain(ratings_summary_db)
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> <SQL>
#> SELECT "Year", COUNT(*) AS "N", AVG("YourRating") AS "AvgRating"
#> FROM "ratings"
#> GROUP BY "Year"
#> ORDER BY "Year"
#> 
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> <PLAN>
#> Sort  (cost=91.70..92.20 rows=200 width=44)
#>   Sort Key: ratings."Year"
#>   ->  HashAggregate  (cost=79.56..82.06 rows=200 width=44)
#>         Group Key: ratings."Year"
#>         ->  Seq Scan on ratings  (cost=0.00..67.32 rows=1632 width=8)

Une base de données relationnelle

  • Schéma "partiel" des tables du package nycflights13.

Exercices (Partie I)

  • Connectez vous à la base "datawarehouse" en tant que "rdb".

  • Créez une table "mtcars" avec le jeu de données mtcars.
    Comparez les fonctions dplyr::copy_to() (en utilisant l'argument temporary) et DBI::dbWriteTable().

  • Modifiez mtcars en passant les noms de lignes dans une colonne et en ajoutant une colonne constructeur const.
    Exporter l'objet mtcars vers la base.
    Que se passe-t'il ? (argument overwrite).

  • Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
    Vérifiez que les tables sont bien créées.

  • Effectuez la jointure de planeset flights pour identifier les 3 constructeurs présentant le plus grand nombre d'heures de vol (?dplyr::join).
    Quelle est la requête SQL réalisée ?

Exercices (Partie I)

  • Connectez vous à la base "datawarehouse" en tant que "rdb".

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "datawarehouse",
      port = 5432,
      uid = "data_user",
      password = "data_pwd"
    )

Exercices (Partie I)

  • Créez une table "mtcars" avec le jeu de données mtcars.
    Comparez les fonctions dplyr::copy_to() (en utilisant l'argument temporary) et DBI::dbWriteTable().

    copy_to(con, mtcars, "mtcars", temporary = TRUE, overwrite = TRUE)
    dbListTables(con)
    #> [1] "ratings"     "ratings2017" "ratings_idx"
    copy_to(con, mtcars, "mtcars", temporary = FALSE, overwrite = TRUE)
    dbListTables(con)
    #> [1] "mtcars"      "ratings"     "ratings2017" "ratings_idx"
    dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
    dbListTables(con)
    #> [1] "mtcars"      "ratings"     "ratings2017" "ratings_idx"

Exercices (Partie I)

  • Modifiez mtcars en passant les noms de lignes dans une colonne et en ajoutant une colonne constructeur const.
    Exporter l'objet mtcars vers la base.
    Que se passe-t'il ? (argument overwrite).

    mtcars %>% 
      rownames_to_column(var = "car") %>% 
      mutate(const = gsub(" .*", "", car)) %>% 
      select(const, everything()) %>% 
      dbWriteTable(con, "mtcars", ., overwrite = TRUE)

Exercices (Partie I)

  • Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
    Vérifiez que les tables sont bien créées.

    library(nycflights13)
    dbWriteTable(con, "flights", flights)
    dbWriteTable(con, "airports", airports)
    dbWriteTable(con, "planes", planes)
    dbWriteTable(con, "weather", weather)
    dbWriteTable(con, "airlines", airlines)
    
    dbListTables(con)

Exercices (Partie I)

  • Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
    Vérifiez que les tables sont bien créées.

    library(nycflights13)
    nycflights13_db <- ls("package:nycflights13") %>% 
      lapply(., function(df) { 
        copy_to(
          df = get(df), 
          dest = con, 
          name = df, 
          overwrite = TRUE, 
          temporary = TRUE
        ) 
      }) %>% 
      `names<-`(ls("package:nycflights13"))
    dbListTables(con)
    #> [1] "mtcars"      "ratings"     "ratings2017" "ratings_idx"

Exercices (Partie I)

  • Effectuez la jointure de planeset flights pour identifier les 3 constructeurs présentant le plus grand nombre d'heures de vol (?dplyr::join).

    longest_air_time_db <- nycflights13_db[["flights"]] %>%
      inner_join(nycflights13_db[["planes"]], by = "tailnum") %>%
      group_by(manufacturer) %>% 
      summarise(total_air_time = sum(air_time)/60) %>% 
      arrange(desc(total_air_time)) %>%
      select(manufacturer, total_air_time) %>% 
      head(3)
    longest_air_time_db
    #> # Source:     lazy query [?? x 2]
    #> # Database:   postgres [data_user@localhost:/datawarehouse]
    #> # Ordered by: desc(total_air_time)
    #>   manufacturer     total_air_time
    #>   <chr>                     <dbl>
    #> 1 BOEING                   296118
    #> 2 AIRBUS                   153993
    #> 3 AIRBUS INDUSTRIE          96442

Exercices (Partie I)

  • Effectuez la jointure de planeset flights pour identifier les 3 constructeurs présentant le plus grand nombre d'heures de vol (?dplyr::join).
    Quelle est la requête SQL réalisée ?

    longest_air_time_db %>% show_query()
    #> <SQL>
    #> SELECT "manufacturer", "total_air_time"
    #> FROM (SELECT *
    #> FROM (SELECT "manufacturer", SUM("air_time") / 60.0 AS "total_air_time"
    #> FROM (SELECT "TBL_LEFT"."year" AS "year.x", "TBL_LEFT"."month" AS "month", "TBL_LEFT"."day" AS "day", "TBL_LEFT"."dep_time" AS "dep_time", "TBL_LEFT"."sched_dep_time" AS "sched_dep_time", "TBL_LEFT"."dep_delay" AS "dep_delay", "TBL_LEFT"."arr_time" AS "arr_time", "TBL_LEFT"."sched_arr_time" AS "sched_arr_time", "TBL_LEFT"."arr_delay" AS "arr_delay", "TBL_LEFT"."carrier" AS "carrier", "TBL_LEFT"."flight" AS "flight", "TBL_LEFT"."tailnum" AS "tailnum", "TBL_LEFT"."origin" AS "origin", "TBL_LEFT"."dest" AS "dest", "TBL_LEFT"."air_time" AS "air_time", "TBL_LEFT"."distance" AS "distance", "TBL_LEFT"."hour" AS "hour", "TBL_LEFT"."minute" AS "minute", "TBL_LEFT"."time_hour" AS "time_hour", "TBL_RIGHT"."year" AS "year.y", "TBL_RIGHT"."type" AS "type", "TBL_RIGHT"."manufacturer" AS "manufacturer", "TBL_RIGHT"."model" AS "model", "TBL_RIGHT"."engines" AS "engines", "TBL_RIGHT"."seats" AS "seats", "TBL_RIGHT"."speed" AS "speed", "TBL_RIGHT"."engine" AS "engine"
    #>   FROM "flights" AS "TBL_LEFT"
    #>   INNER JOIN "planes" AS "TBL_RIGHT"
    #>   ON ("TBL_LEFT"."tailnum" = "TBL_RIGHT"."tailnum")
    #> ) "zlzjmmynwb"
    #> GROUP BY "manufacturer") "otzkpogcwa"
    #> ORDER BY "total_air_time" DESC) "bgjtdarjkv"
    #> LIMIT 3

Exercices (Partie II)

  • Calculez la moyenne des températures temp (table weather) par station origin et par année year.
    Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).

  • Comptez le nombre d'aéroport dont le code dest (table flights) commence par la lettre "A" (Wildcard SQL; %like%).

  • Créez une nouvelle colonne origin_dest à partir de origin et dest.

Exercices (Partie II)

  • Calculez la moyenne des températures temp (table weather) par station origin et par année year.
    Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).

    nycflights13::weather %>% 
      group_by(origin, year) %>%  
      summarise(avg_temp = mean(temp))
    #> # A tibble: 3 x 3
    #> # Groups:   origin [?]
    #>   origin  year avg_temp
    #>   <chr>  <dbl>    <dbl>
    #> 1 EWR     2013     NA  
    #> 2 JFK     2013     54.4
    #> 3 LGA     2013     55.7
    nycflights13_db[["weather"]] %>% 
      group_by(origin, year) %>% 
      summarise(avg_temp = mean(temp))

Exercices (Partie II)

  • Calculez la moyenne des températures temp (table weather) par station origin et par année year.
    Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).

    nycflights13::weather %>% 
      group_by(origin, year) %>%  
      summarise(avg_temp = mean(temp))
    nycflights13_db[["weather"]] %>% 
      group_by(origin, year) %>% 
      summarise(avg_temp = mean(temp))
    #> # Source:   lazy query [?? x 3]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #> # Groups:   origin
    #>   origin  year avg_temp
    #>   <chr>  <dbl>    <dbl>
    #> 1 JFK     2013     54.4
    #> 2 EWR     2013     55.5
    #> 3 LGA     2013     55.7
    #> # ... with more rows

Exercices (Partie II)

  • Comptez le nombre d'aéroport dont le code dest (table flights) commence par la lettre "A" (Wildcard SQL; %like%).

    nycflights13_db[["flights"]] %>% 
      filter(dest %like% 'A%') %>% 
      summarise(count = n_distinct(dest))
    #> # Source:   lazy query [?? x 1]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   count          
    #>   <S3: integer64>
    #> 1 7

Exercices (Partie II)

  • Créez une nouvelle colonne origin_dest à partir de origin et dest.

    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = origin %|| '-' ||% dest) %>%
      select(origin, dest, origin_dest)
    #> # Source:   lazy query [?? x 3]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   origin dest  origin_dest
    #>   <chr>  <chr> <chr>      
    #> 1 EWR    IAH   EWR-IAH    
    #> 2 LGA    IAH   LGA-IAH    
    #> # ... with more rows
    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
      select(origin, dest, origin_dest)
    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
      select(origin, dest, origin_dest)

Exercices (Partie II)

  • Créez une nouvelle colonne origin_dest à partir de origin et dest.

    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = origin %|| '-' ||% dest) %>%
      select(origin, dest, origin_dest)
    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
      select(origin, dest, origin_dest)
    #> # Source:   lazy query [?? x 3]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   origin dest  origin_dest
    #>   <chr>  <chr> <chr>      
    #> 1 EWR    IAH   EWR-IAH    
    #> 2 LGA    IAH   LGA-IAH    
    #> # ... with more rows
    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
      select(origin, dest, origin_dest)

Exercices (Partie II)

  • Créez une nouvelle colonne origin_dest à partir de origin et dest.

    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = origin %|| '-' ||% dest) %>%
      select(origin, dest, origin_dest)
    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
      select(origin, dest, origin_dest)
    nycflights13_db[["flights"]] %>%
      mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
      select(origin, dest, origin_dest)
    #> # Source:   lazy query [?? x 3]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   origin dest  origin_dest
    #>   <chr>  <chr> <chr>      
    #> 1 EWR    IAH   EWR-IAH    
    #> 2 LGA    IAH   LGA-IAH    
    #> # ... with more rows

Schéma et table

  • Une base de données contenant des schémas décrivant des tables.

    dbSendQuery(con, "CREATE SCHEMA nycflights13;")
    dbWriteTable(con, SQL("nycflights13.flights"), flights)
    #> <OdbcResult>
    #>   SQL  CREATE SCHEMA nycflights13;
    #>   ROWS Fetched: 0 [complete]
    #>        Changed: 71285632

Schéma et table

  • La fonction tbl() cherche dans le schéma par défaut ("public" sur PostgreSQL).

    tbl(con, "mtcars") %>% head()
    #> # Source:   lazy query [?? x 13]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   const car     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #>   <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1 Mazda Mazd~  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2 Mazda Mazd~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3 Dats~ Dats~  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> 4 Horn~ Horn~  21.4  6.00   258 110    3.08  3.22  19.4  1.00  0     3.00  1.00
    #> 5 Horn~ Horn~  18.7  8.00   360 175    3.15  3.44  17.0  0     0     3.00  2.00
    #> # ... with more rows

Schéma et table

  • dplyr::ìn_schema() permet de spécifier le schéma à utiliser.

    tbl(con, in_schema("public", "mtcars")) %>% head()
    #> # Source:   lazy query [?? x 13]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   const car     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    #>   <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
    #> 1 Mazda Mazd~  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
    #> 2 Mazda Mazd~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
    #> 3 Dats~ Dats~  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
    #> 4 Horn~ Horn~  21.4  6.00   258 110    3.08  3.22  19.4  1.00  0     3.00  1.00
    #> 5 Horn~ Horn~  18.7  8.00   360 175    3.15  3.44  17.0  0     0     3.00  2.00
    #> # ... with more rows

Schéma et table

  • Accéder à une table présente dans un schéma autre que celui par défaut.

    tbl(con, in_schema("nycflights13", "flights")) %>% head(1)
    #> # Source:   lazy query [?? x 19]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
    #>   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
    #> 1  2013     1     1      517            515      2.00      830            819
    #> # ... with 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
    #> #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
    #> #   hour <dbl>, minute <dbl>, time_hour <dttm>
    tbl(con, in_schema("nycflights13", "weather")) %>% head(1)
    #> # Source:   lazy query [?? x 15]
    #> # Database: postgres [data_user@localhost:/datawarehouse]
    #>   origin  year month   day  hour  temp  dewp humid wind_dir wind_speed
    #>   <chr>  <dbl> <dbl> <int> <int> <dbl> <dbl> <dbl>    <dbl>      <dbl>
    #> 1 EWR     2013  1.00     1     0  37.0  21.9  54.0      230       10.4
    #> # ... with 5 more variables: wind_gust <dbl>, precip <dbl>, pressure <dbl>,
    #> #   visib <dbl>, time_hour <dttm>

Pour aller plus loin …

rvest

Extraction de contenus web

Protocole HTTP et Web

Protocole HTTP et Web

Protocole HTTP et Web

La page de l'IMDb

Stratégie

Stratégie

  • Identifier les balises HTML encapsulant l'information.
  • Utiliser l'architecture en arbre du code HTML.

Stratégie

  • Identifier les balises HTML encapsulant l'information.
  • Utiliser l'architecture en arbre du code HTML et du CSS.

Pré-requis

library(xml2)
library(rvest)
library(tidyverse)
tidyverse_conflicts()
#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor()     masks scales::col_factor()
#> x purrr::discard()        masks scales::discard()
#> x dplyr::filter()         masks stats::filter()
#> x rvest::guess_encoding() masks readr::guess_encoding()
#> x rvest::html()           masks Hmisc::html()
#> x dbplyr::ident()         masks dplyr::ident()
#> x dplyr::lag()            masks stats::lag()
#> x pryr::partial()         masks purrr::partial()
#> x rvest::pluck()          masks purrr::pluck()
#> x dbplyr::sql()           masks dplyr::sql()
#> x dplyr::src()            masks Hmisc::src()
#> x dplyr::summarize()      masks Hmisc::summarize()

rvest : Les étapes clés

  1. Télécharger le code HTML et le convertir en XML.

    read_html()
  2. Extraire les noeuds.

    html_nodes()
  3. Extraire le contenu des noeuds.
    • html_text()
    • html_name()
    • html_attrs()
    • html_children()
    • html_table()
    imdb_xml %>% 
      html_nodes("h1") %>% 
      html_text()

rvest : Les étapes clés

  • Récupérer l'URL de la page casting du film.

    rpo_cast_url <- "https://www.imdb.com/title/tt1677720/fullcredits/"

rvest : Les étapes clés

  • Récupérer la table contenant les acteurs et les personnages du film.

    rpo_cast_list <- read_html(rpo_cast_url) %>% 
      html_nodes("table.cast_list")

rvest : Exemple avec l'IMDb

  • Récupérer la liste des personnages du film.

    rpo_character_list <- rpo_cast_list %>% 
      html_nodes("td.character")

rvest : Exemple avec l'IMDb

  • Extraire le contenu des noeuds.

    html_text(rpo_character_list) %>% head(2)
    #> [1] "\n              \n            Parzival /  \n            Wade \n                      \n\n              \n          "   
    #> [2] "\n              \n            Art3mis /  \n            Samantha \n                      \n\n              \n          "
  • Formater le contenu.

    rpo_character_list %>% 
      html_text() %>% 
      gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .) %>% 
      head(2)
    #> [1] "Parzival / Wade"    "Art3mis / Samantha"

rvest : Exemple avec l'IMDb

  • Reconstruction du tableau acteur/personnage.

    cast_tbl <- tibble(
      Actor = read_html(rpo_cast_url) %>% 
        html_nodes("td.itemprop") %>% 
        html_nodes("span.itemprop") %>% 
        html_text(),
      Character = rpo_character_list %>% 
        html_text() %>% 
        gsub("\n", "", .) %>% 
        gsub(" +", " ", .) %>% 
        gsub("^ (.*) $", "\\1", .)
    )
    
    cast_tbl %>% 
      head(3)
    #> # A tibble: 3 x 2
    #>   Actor          Character         
    #>   <chr>          <chr>             
    #> 1 Tye Sheridan   Parzival / Wade   
    #> 2 Olivia Cooke   Art3mis / Samantha
    #> 3 Ben Mendelsohn Sorrento

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Lecture du fichier contenant les notes des films.

    ratings <- read_csv("materials/ratings.csv") %>% 
      `colnames<-`(gsub(" ", "", colnames(.))) %>% 
      mutate(
        Year = lubridate::year(DateRated),
        Month = lubridate::month(DateRated),
        Day = lubridate::wday(DateRated)
      )

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Création d'un nouveau utilisateur et d'une nouvelle base.

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "postgres",
      port = 5432,
      uid = "postgres",
      password = "password"
    )
    dbSendQuery(con, SQL("CREATE ROLE imdb_user LOGIN password 'imdb_pwd';"))
    dbSendQuery(con, SQL("CREATE DATABASE movies;"))
    dbSendQuery(con, SQL("ALTER DATABASE movies OWNER TO imdb_user;"))
    dbDisconnect(con)

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Connection avec "imdb_user" sur "movies".

    con <- dbConnect(
      drv = odbc(),
      driver = "PostgreSQL ODBC Driver(UNICODE)",
      server = "localhost",
      database = "movies",
      port = 5432,
      uid = "imdb_user",
      password = "imdb_pwd"
    )

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Création de la table "ratings" (et d'un objet tbl_sql).

    ratings_db <- copy_to(
      dest = con, 
      df = ratings, 
      names = "ratings", 
      overwrite = TRUE,
      temporary = FALSE
    )

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Extraction du casting d'un film provenant de la table "ratings".

    cast_list <- ratings_db %>% 
      filter(Title %like% "Ready Player One") %>% 
      collect() %>% 
      .[["URL"]] %>% 
      paste0(., "fullcredits/") %>% 
      read_html() %>% 
      html_nodes("table.cast_list")

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Création d'un tableau casting.

    cast_tbl <- tibble(
      Actor = cast_list %>% 
        html_nodes("td.itemprop") %>% 
        html_nodes("span.itemprop") %>% 
        html_text(),
      Character = cast_list %>% 
        html_nodes("td.character") %>% 
        html_text() %>% 
        gsub("\n", "", .) %>% 
        gsub(" +", " ", .) %>% 
        gsub("^ (.*) $", "\\1", .)
    )

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Fontion de récuppération du casting d'un film.

    get_cast <- function(x) {
      x %>% 
        paste0(., "fullcredits/") %>% 
        read_html() %>% 
        html_nodes("table.cast_list") %>% 
        (function(node) {
          cast_tbl <- tibble(
            URL = x,
            Actor = node %>% 
              html_nodes("td.itemprop") %>% 
              html_nodes("span.itemprop") %>% 
              html_text(),
            Character = node %>% 
              html_nodes("td.character") %>%
              html_text() %>% 
              gsub("\n", "", .) %>% 
              gsub(" +", " ", .) %>% 
              gsub("^ (.*) $", "\\1", .)
          )
        }) %>% 
        return()
    }

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Fontion de récuppération du casting d'un film.

    get_cast("https://www.imdb.com/title/tt0100240/")
    #> # A tibble: 36 x 3
    #>   URL                                   Actor             Character
    #>   <chr>                                 <chr>             <chr>    
    #> 1 https://www.imdb.com/title/tt0100240/ Jonathan Brandis  Bastian  
    #> 2 https://www.imdb.com/title/tt0100240/ Kenny Morrison    Atreyu   
    #> 3 https://www.imdb.com/title/tt0100240/ Clarissa Burt     Xayide   
    #> 4 https://www.imdb.com/title/tt0100240/ John Wesley Shipp Barney   
    #> 5 https://www.imdb.com/title/tt0100240/ Martin Umbach     Nimbly   
    #> # ... with 31 more rows

rvest, DBI, dplyr, dbplyr et le tidyverse

  • Ajout du casting dans ratings_db (local).

    casting <- ratings_db %>% 
      select(URL) %>% 
      collect() %>% 
      head() %>% 
      mutate(
        Casting = map(URL, get_cast)
      )
    casting
    #> # A tibble: 6 x 2
    #>   URL                                   Casting           
    #>   <chr>                                 <list>            
    #> 1 https://www.imdb.com/title/tt1001508/ <tibble [121 x 3]>
    #> 2 https://www.imdb.com/title/tt1001526/ <tibble [19 x 3]> 
    #> 3 https://www.imdb.com/title/tt0100240/ <tibble [36 x 3]> 
    #> 4 https://www.imdb.com/title/tt1002966/ <tibble [43 x 3]> 
    #> 5 https://www.imdb.com/title/tt0100403/ <tibble [65 x 3]> 
    #> # ... with 1 more row

Exercices

En utilisant le lien url : https://www.imdb.com/user/ur56341222/ratings.

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  • Compilez des statistiques (moyenne, écart-type, etc.) dans une nouvelle table.

  • Exportez ces tables vers une base de données.

Exercices

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

    movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
      read_html() %>% 
      html_nodes("div#ratings-container.lister-list") %>% 
      html_nodes("div.lister-item.mode-detail")
    • Titre.
    title = x %>% 
      html_nodes("h3.lister-item-header") %>% 
      html_text() %>% 
      gsub("\n", "", .) %>% 
      gsub(" [0-9]+.", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .)

Exercices

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

    movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
      read_html() %>% 
      html_nodes("div#ratings-container.lister-list") %>% 
      html_nodes("div.lister-item.mode-detail")
    • Genres.
    genres = x %>% 
      html_nodes("span.genre") %>% 
      html_text() %>% 
      gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .)

Exercices

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

    movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
      read_html() %>% 
      html_nodes("div#ratings-container.lister-list") %>% 
      html_nodes("div.lister-item.mode-detail")
    • Durée du film.
    runtime = x %>% 
      html_nodes("span.runtime") %>% 
      html_text() %>% 
      gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .)

Exercices

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

    movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
      read_html() %>% 
      html_nodes("div#ratings-container.lister-list") %>% 
      html_nodes("div.lister-item.mode-detail")
    • Note.
    rating = x %>% 
      html_nodes("div.ipl-rating-widget") %>% 
      html_nodes("div.ipl-rating-star.small") %>% 
      html_text("span.ipl-rating-star__rating") %>% 
      unique() %>% 
      gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .) %>% 
      as.numeric() %>% 
      (function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})

Exercices

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

    get_movies_info <- function(x) {
      c(
        x %>% html_nodes("h3.lister-item-header") %>% html_text() %>% 
          gsub("\n", "", .) %>% gsub(" [0-9]+.", "", .) %>% 
          gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),
    
        x %>% html_nodes("span.genre") %>% html_text() %>% 
          gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),
    
        x %>% html_nodes("span.runtime") %>% html_text() %>% 
          gsub("\n", "", .) %>% gsub(" +", " ", .) %>% 
          gsub("^ (.*) $", "\\1", .) %>% ifelse(length(.)==0, NA, .),
    
        x %>% html_nodes("div.ipl-rating-widget") %>% 
          html_nodes("div.ipl-rating-star.small") %>% 
          html_text("span.ipl-rating-star__rating") %>% 
          gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .) %>% 
          as.numeric() %>%(function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})
      ) %>% 
        `names<-`(c("title", "genres", "runtime", "imdbrating", "userating")) %>% 
        return()
    }

Exercices

  • Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

    movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
      read_html() %>% 
      html_nodes("div#ratings-container.lister-list") %>% 
      html_nodes("div.lister-item.mode-detail") %>% 
      lapply(., get_movies_info) %>% 
      do.call("bind_rows", .)

ggplot2

The grammar of graphics

Simplifier et améliorer la création

nodbi

Le connecteur pour les bases NoSQL

nodbi (développement en cours)

  • nodbi sur GitHub : nodbi

Supporte :

  • MongoDB
  • Redis
  • CouchDB
  • Elasticsearch
  • etcd
  • Riak

nodbi (développement en cours)

  • Package sur le CRAN :
    • sofa (CouchDB) CRAN.
    • mongolite (MongoDB) CRAN.
    • elastic (Elasticsearch) CRAN.
    • etseed (etcd) CRAN.
  • Package sur hors CRAN :

Merci ! Présentation disponible au format Rmarkdown sur